/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
 *
 * @author moustafa
 */
public class DataWrapper {
    private DataSource datasource;
    private static DataWrapper wrapperInstance = null;

    public static DataWrapper getInstance(){
        if(DataWrapper.wrapperInstance == null)
            DataWrapper.wrapperInstance = new DataWrapper();
        return DataWrapper.wrapperInstance;
    }

    private DataWrapper(){
        try {
            InitialContext context = new InitialContext();
            datasource = (DataSource) context.lookup("jdbc/oesDS");
            
        } catch (NamingException ex) {
            Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public Connection getConnection(){
        Connection conn = null;
        try {
            conn = this.datasource.getConnection();
            
        } catch (SQLException ex) {
            Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
        }
        return conn;
    }

    public ArrayList<Warehouse> getWarehouses(Connection connection){
        ArrayList<Warehouse> warehouses = new ArrayList<Warehouse>();
        try {
            Statement stmt = connection.createStatement();
            stmt.executeQuery("SELECT * FROM oes_data.warehouses;");
            ResultSet rs = stmt.getResultSet();
            //javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame("Message"), rs);
            while(rs.next()){
                warehouses.add(new Warehouse(rs.getInt("warehouse_id"), rs.getString("warehouse_name"), rs.getString("warehouse_location")));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return warehouses;
    }

    public ArrayList<Inventory> getInventories(Connection connection){
        ArrayList<Inventory> inventories = new ArrayList<Inventory>();
        try {
            Statement stmt = connection.createStatement();
            stmt.executeQuery("SELECT       warehouses.warehouse_id ,  product_information.product_id , quantity_on_hand "+
                                "FROM       oes_data.warehouses , oes_data.product_information , oes_data.inventories "+
                                "WHERE      inventories.product_id = product_information.product_id "+
                                "AND        inventories.warehouse_id = warehouses.warehouse_id "+
                                "ORDER BY   warehouse_name , product_name;");
            ResultSet rs = stmt.getResultSet();
            //javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame("Message"), rs);
            while(rs.next()){
               // javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame("Message"), getProduct(rs.getInt("product_information.product_id"), getConnection()));
                inventories.add(new Inventory(getWarehouse(rs.getInt("warehouses.warehouse_id"), getConnection()) , getProduct(rs.getInt("product_information.product_id"), getConnection()) , rs.getInt("quantity_on_hand")));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
        }try {
            connection.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
        }
        return inventories;
    }

    public ArrayList<Inventory> getDeficientInventories(Connection connection){
        int quantityThreshold = Inventory.QUANTITY_THRESHOLD;
        ArrayList<Inventory> inventories = new ArrayList<Inventory>();
        try {
            PreparedStatement stmt = connection.prepareStatement("SELECT       warehouses.warehouse_id ,  product_information.product_id , quantity_on_hand "+
                                "FROM       oes_data.warehouses , oes_data.product_information , oes_data.inventories "+
                                "WHERE      inventories.product_id = product_information.product_id "+
                                "AND        inventories.warehouse_id = warehouses.warehouse_id "+
                                "AND        quantity_on_hand < ? "+
                                "ORDER BY   warehouse_name , product_name;");
            stmt.setInt(1, quantityThreshold);
            ResultSet rs = stmt.executeQuery();
            //javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame("Message"), rs);
            while(rs.next()){
               // javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame("Message"), getProduct(rs.getInt("product_information.product_id"), getConnection()));
                inventories.add(new Inventory(getWarehouse(rs.getInt("warehouses.warehouse_id"), getConnection()) , getProduct(rs.getInt("product_information.product_id"), getConnection()) , rs.getInt("quantity_on_hand")));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
        }try {
            connection.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
        }
        return inventories;
    }

    public ArrayList<Inventory> getRecoveryInventories(Inventory targetInv, Connection connection){
        int quantityThreshold = Inventory.QUANTITY_THRESHOLD;
        int requiredQuantiry = quantityThreshold + (quantityThreshold - targetInv.getQuantity());
        ArrayList<Inventory> inventories = new ArrayList<Inventory>();
        try {
            PreparedStatement stmt = connection.prepareStatement("SELECT       warehouses.warehouse_id ,  product_information.product_id , quantity_on_hand "+
                                "FROM       oes_data.warehouses , oes_data.product_information , oes_data.inventories "+
                                "WHERE      inventories.product_id = product_information.product_id "+
                                "AND        inventories.warehouse_id = warehouses.warehouse_id "+
                                "AND        quantity_on_hand >= ? "+
                                "ORDER BY   warehouse_name , product_name;");
            stmt.setInt(1, requiredQuantiry);
            ResultSet rs = stmt.executeQuery();
            //javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame("Message"), rs);
            while(rs.next()){
               // javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame("Message"), getProduct(rs.getInt("product_information.product_id"), getConnection()));
                inventories.add(new Inventory(getWarehouse(rs.getInt("warehouses.warehouse_id"), getConnection()) , getProduct(rs.getInt("product_information.product_id"), getConnection()) , rs.getInt("quantity_on_hand")));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
        }try {
            connection.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
        }
        return inventories;
    }

    public Product getProduct(int productID, Connection connection){
        Product product = null;
        try {
            PreparedStatement stmt = connection.prepareStatement("SELECT * FROM oes_data.product_information WHERE product_id = ?;");
            stmt.setInt(1, productID);
            ResultSet rs =  stmt.executeQuery();
            //javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame("Message"), rs);
            while(rs.next()){
                product = new Product(rs.getInt("product_id"), rs.getString("product_name"), rs.getString("product_description"), rs.getString("product_image"), rs.getString("product_status"), rs.getFloat("list_price"), rs.getFloat("min_price"), rs.getString("catalog_url"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return product;
    }

    public Warehouse getWarehouse(int warehouseID, Connection connection){
        Warehouse warehouse = null;
        try {
            PreparedStatement stmt = connection.prepareStatement("SELECT * FROM oes_data.warehouses WHERE warehouse_id = ?;");
            stmt.setInt(1, warehouseID);
            ResultSet rs = stmt.executeQuery();
            //javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame("Message"), rs);
            while(rs.next()){
                warehouse = new Warehouse(rs.getInt("warehouse_id"), rs.getString("warehouse_name"), rs.getString("warehouse_location"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return warehouse;

    }

    public void addWarehouse(Warehouse warehouse, Connection conn){
        try {
            PreparedStatement stmt = conn.prepareStatement("INSERT INTO oes_data.warehouses (warehouse_name, warehouse_location) VALUES ( ? , ?)");
            stmt.setString(1, warehouse.getName());
            stmt.setString(2, warehouse.getLocation());
            stmt.executeUpdate();
            
        } catch (SQLException ex) {
            Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public void addInventory(Inventory inventory, Connection conn){
        try {
            PreparedStatement stmt = conn.prepareStatement("INSERT INTO oes_data.inventories (product_id, warehouse_id, quantity_on_hand) VALUES ( ? , ? , ?)");
            stmt.setInt(1, inventory.getProduct().getID());
            stmt.setInt(2, inventory.getWarehouse().getID());
            stmt.setInt(3, inventory.getQuantity());
            stmt.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public void updateWarehouse(Warehouse warehouse, Connection connection) {
        try {
            PreparedStatement stmt = connection.prepareStatement("UPDATE oes_data.warehouses SET warehouse_name=?, warehouse_location=? WHERE warehouse_id=?");
            stmt.setString(1, warehouse.getName());
            stmt.setString(2, warehouse.getLocation());
            stmt.setInt(3, warehouse.getID());
            stmt.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public ArrayList<Product> getProducts(Connection connection) {
        ArrayList<Product> products = new ArrayList<Product>();
        try {
            Statement stmt = connection.createStatement();
            stmt.executeQuery("SELECT * FROM oes_data.product_information;");
            ResultSet rs = stmt.getResultSet();
            //javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame("Message"), rs);
            while(rs.next()){
                products.add(new Product(rs.getInt("product_id"), rs.getString("product_name"), rs.getString("product_description"), rs.getString("product_image"), rs.getString("product_status"), rs.getFloat("list_price"), rs.getFloat("min_price"), rs.getString("catalog_url")));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return products;
    }

    public void closeConnection(Connection connection){
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
            }
    }

    public void updateInventory(Inventory inventory, Connection connection) {
        try {
            PreparedStatement stmt = connection.prepareStatement("UPDATE oes_data.inventories SET quantity_on_hand=? WHERE warehouse_id=? AND product_id=?");
            stmt.setInt(1, inventory.getQuantity());
            stmt.setInt(2, inventory.getWarehouseID());
            stmt.setInt(3, inventory.getProductID());
            stmt.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public void addProduct(Product product, Connection connection) {
      try {
            PreparedStatement stmt = connection.prepareStatement("INSERT INTO oes_data.product_information (product_name, product_description, product_image, product_status, list_price, min_price, catalog_url) VALUES ( ? , ? , ? , ? , ? , ? , ?)");
            stmt.setString(1, product.getName());
            stmt.setString(2, product.getDescription());
            stmt.setString(3, product.getImageURL());
            stmt.setString(4, product.getStatus());
            stmt.setFloat(5, product.getListPrice());
            stmt.setFloat(6, product.getMinPrice());
            stmt.setString(7, product.getCatalogURL());
            stmt.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public void updateProduct(Product product, Connection connection) {
        try {
            PreparedStatement stmt = connection.prepareStatement("UPDATE oes_data.product_information SET product_name=?, product_description=?, product_image=?, product_status=?, list_price=?, min_price=?, catalog_url=? WHERE product_id=?");
            stmt.setString(1, product.getName());
            stmt.setString(2, product.getDescription());
            stmt.setString(3, product.getImageURL());
            stmt.setString(4, product.getStatus());
            stmt.setFloat(5, product.getListPrice());
            stmt.setFloat(6, product.getMinPrice());
            stmt.setString(7, product.getCatalogURL());
            stmt.setInt(8, product.getID());
            stmt.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataWrapper.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public void recover(Inventory targetInv, Inventory sourceInv) {
        int defficiency = Inventory.QUANTITY_THRESHOLD - targetInv.getQuantity();
        targetInv.setQuantity(Inventory.QUANTITY_THRESHOLD);
        sourceInv.setQuantity(sourceInv.getQuantity() - defficiency);
        updateInventory(targetInv, getConnection());
        updateInventory(sourceInv, getConnection());
    }
}
